package com.sky.service.impl;

import com.sky.dto.GoodsSalesDTO;
import com.sky.entity.Orders;
import com.sky.mapper.OrderMapper;
import com.sky.mapper.UserMapper;
import com.sky.service.ReportService;
import com.sky.service.WorkspaceService;
import com.sky.vo.*;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.auditing.IsNewAwareAuditingHandler;
import org.springframework.stereotype.Service;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

@Service
public class ReportServiceImpl implements ReportService {

    @Autowired
    private OrderMapper orderMapper;

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private WorkspaceService workspaceService;
    /**
     * 统计指定日期之间的营业额数据
     * @param begin
     * @param end
     */
    public TurnoverReportVO turnoverStatistics(LocalDate begin, LocalDate end) {
        //两个数据：一个是datelist，begin到end之间的日期，转换为字符串  另一个是对应的营业额
        List<LocalDate> dateTimeList = new ArrayList<>();

        dateTimeList.add(begin);

        while(!begin.equals(end)){
            begin = begin.plusDays(1);   //日期后一天移动
            dateTimeList.add(begin);
        }

        String datelist = StringUtils.join(dateTimeList,",");  //使用工具类转换，或者stream流，或者遍历转换后拼接

        List<Double> saleSumList = new ArrayList<>();   //存放某天的营业额

        //第二个数据，对应日期的营业额  数据库查询
        for(LocalDate date : dateTimeList){
            LocalDateTime beginTime = LocalDateTime.of(date, LocalTime.MIN);//每天的初始时间00：00：00
            LocalDateTime endTime = LocalDateTime.of(date,LocalTime.MAX);   //23:59:59

           Double saleDaily =  orderMapper.sumByTime(beginTime,endTime, Orders.COMPLETED);

           //如果没有营业额，应该转换为0,比较合理，不然不显示了
           if(saleDaily == null){
               saleDaily = 0.0;
           }

           saleSumList.add(saleDaily);

        }

        //转换成字符串对象
        String saleList = StringUtils.join(saleSumList, ',');

        //封装对象
        TurnoverReportVO turnoverReportVO = new TurnoverReportVO();
        turnoverReportVO.setDateList(datelist);
        turnoverReportVO.setTurnoverList(saleList);

        return turnoverReportVO;
    }

    /**
     * 用户数据统计接口
     */
    public UserReportVO userStatistics(LocalDate begin, LocalDate end) {
        //三哥数据  datelist，totalUsers , new User

        //1.datelist   2021-2-9,2021-2-10...
        List<LocalDate> dateList = new ArrayList<>();

        dateList.add(begin);
        while(!begin.equals(end)){
            begin = begin.plusDays(1);
            dateList.add(begin);
        }

        String dateLists = StringUtils.join(dateList, ",");

        //2.totalUsers    //当天的总用户量
        List<Integer> totalUsersList = new ArrayList<>();

        for(LocalDate localDate : dateList){
            LocalDateTime endTime = LocalDateTime.of(localDate,LocalTime.MAX);   //定义当天的最晚结束时间

            //select count(id) from user where create_time < endTime    //当天结束前的用户数量
            Integer totalUserOneDay = userMapper.totalUsersByDate(endTime);

            if(totalUserOneDay == null) totalUserOneDay = 0;   //如果没有用户，则改为0，否则是空的
            totalUsersList.add(totalUserOneDay);

        }
        //转换为字符串
        String totalUsers = StringUtils.join(totalUsersList, ",");

        //3.newUser   //新增用户数
        List<Integer> newUserList = new ArrayList<>();

        for(LocalDate localDate : dateList){
            LocalDateTime beginTime = LocalDateTime.of(localDate, LocalTime.MIN);//每天的00：00：00
            LocalDateTime endTime = LocalDateTime.of(localDate,LocalTime.MAX);

            Integer newUser = userMapper.CntnewUser(beginTime,endTime);
            if(newUser == null) newUser = 0;

            newUserList.add(newUser);
        }

        String newUsers = StringUtils.join(newUserList, ",");

        //封装VO对象
        UserReportVO userReportVO = UserReportVO.builder()
                .newUserList(newUsers)
                .totalUserList(totalUsers)
                .dateList(dateLists)
                .build();

        return userReportVO;
    }


    /**
     * 订单统计
     * @param begin
     * @param end
     * @return
     */
    public OrderReportVO ordersStatistics(LocalDate begin, LocalDate end) {
        //1.日期列表
        //1.dateList   2021-2-9,2021-2-10...
        List<LocalDate> dateList = new ArrayList<>();

        dateList.add(begin);
        while(!begin.equals(end)){
            begin = begin.plusDays(1);
            dateList.add(begin);
        }

        String dateLists = StringUtils.join(dateList, ",");

        //2.每日订单总数和有效订单数
        List<Integer> totalOrder = new ArrayList<>();
        List<Integer> validOrder = new ArrayList<>();

        for (LocalDate localDate : dateList) {
            LocalDateTime beginTime = LocalDateTime.of(localDate, LocalTime.MIN);//每天的00：00：00
            LocalDateTime endTime = LocalDateTime.of(localDate,LocalTime.MAX);

            //两个查询，区别在于有效订单需要多判断 status == 5，所以定义一个mapper即可
            Integer allOrderCnt = orderMapper.OrderCnt(beginTime,endTime,null);   //status为空即可
            Integer validOrderCnt = orderMapper.OrderCnt(beginTime,endTime,Orders.COMPLETED);

            if(allOrderCnt == null) allOrderCnt = 0;
            if(validOrderCnt == null) validOrderCnt = 0;

            totalOrder.add(allOrderCnt);
            validOrder.add(validOrderCnt);
        }

        //转字符串
        String totalOrders = StringUtils.join(totalOrder, ",");
        String validOrders = StringUtils.join(validOrder,",");

        //3.求和并计算完成率
        //stream流求和
        Integer totalOrdersNum = totalOrder.stream().reduce(Integer::sum).get();
        Integer validOrdersNum = validOrder.stream().reduce(Integer::sum).get();

        Double orderCompletionRate = 0.0;

        if(validOrdersNum != 0){   //分母非0
            orderCompletionRate = validOrdersNum.doubleValue() / totalOrdersNum;
        }

        //4.封装VO对象
        OrderReportVO orderReportVO = OrderReportVO.builder()
                .orderCompletionRate(orderCompletionRate)
                .totalOrderCount(totalOrdersNum)
                .validOrderCount(validOrdersNum)
                .validOrderCountList(validOrders)
                .orderCountList(totalOrders)
                .dateList(dateLists)
                .build();
        return orderReportVO;
    }

    /**
     *top10
     * @param begin
     * @param end
     */
    public SalesTop10ReportVO top10(LocalDate begin, LocalDate end) {
        //order_detail表可以查到菜品或套餐的数量number,但是可能会对应订单被取消的情况，此时肯定不能算上，所以要用到orders表
        //具体sql看xml文件
        /**
         select od.name, sum(od.number) number from order_detail od,orders o
         where o.id = od.order_id and o.status = 5 and o.order_time > '2023-08-20'
         and o.order_time < '2023-09-30' group by od.name  order by number desc
         limit 0,10
         */
        LocalDateTime beginTime = LocalDateTime.of(begin, LocalTime.MIN);
        LocalDateTime endTime = LocalDateTime.of(end, LocalTime.MAX);

        List<GoodsSalesDTO> goodsSalesDTOS = orderMapper.top10(beginTime,endTime,Orders.COMPLETED);

        //将查到的数据分别提出并转换为相应的列表和字符串
        List<String> names = new ArrayList<>();
        List<Integer> numbers = new ArrayList<>();

        for (GoodsSalesDTO goodsSalesDTO : goodsSalesDTOS) {
            names.add(goodsSalesDTO.getName());
            numbers.add(goodsSalesDTO.getNumber());
        }

        String nameList = StringUtils.join(names, ",");
        String NumberList = StringUtils.join(numbers,",");

        SalesTop10ReportVO salesTop10ReportVO = new SalesTop10ReportVO();
        salesTop10ReportVO.setNameList(nameList);
        salesTop10ReportVO.setNumberList(NumberList);

        return salesTop10ReportVO;
    }

    /**
     * 导出excel数据报表
     */
    public void exportBussinessData(HttpServletResponse response) {
        //1.根据报表模板查询数据库,发现前面几项已经在工作台中查询过了，所以直接调用
        LocalDate begin = LocalDate.now().minusDays(30);//相对今天之前的30天
        LocalDate end = LocalDate.now().minusDays(1);   //今天不算上，从昨天结束

        LocalDateTime beginTime = LocalDateTime.of(begin, LocalTime.MIN);
        LocalDateTime endTime = LocalDateTime.of(end, LocalTime.MAX);

        //查询概览数据
        BusinessDataVO businessData = workspaceService.getBusinessData(beginTime,endTime);
        //封装了相应的五条数据

        //2.通过POI将数据写入excel文件
        InputStream input = this.getClass().getClassLoader().getResourceAsStream("template/运营数据报表模板.xlsx");

        try {
            //基于模板创建excel文件
            XSSFWorkbook excel = new XSSFWorkbook(input);

            //3.填充数据   根据模板文件判断设置元素的位置
            XSSFSheet sheet = excel.getSheet("Sheet1");

            sheet.getRow(1).getCell(1).setCellValue("时间:"+begin+" 至 "+end);

            //根据表格利用POI写入数据
            sheet.getRow(3).getCell(2).setCellValue(businessData.getTurnover());
            sheet.getRow(3).getCell(4).setCellValue(businessData.getOrderCompletionRate());
            sheet.getRow(3).getCell(6).setCellValue(businessData.getNewUsers());
            sheet.getRow(4).getCell(2).setCellValue(businessData.getValidOrderCount());
            sheet.getRow(4).getCell(4).setCellValue(businessData.getUnitPrice());


            //填充明细数据
            for(int i = 0;i < 30;i++){
                LocalDate date = begin.plusDays(i);     //算出30天每天的日期

                //获取当天的数据
                BusinessDataVO data = workspaceService.getBusinessData(LocalDateTime.of(date, LocalTime.MIN), LocalDateTime.of(date, LocalTime.MAX));

                XSSFRow row = sheet.getRow(i + 7);    //获得某一行
                row.getCell(1).setCellValue(date.toString());
                row.getCell(2).setCellValue(data.getTurnover());
                row.getCell(3).setCellValue(data.getValidOrderCount());
                row.getCell(4).setCellValue(data.getOrderCompletionRate());
                row.getCell(5).setCellValue(data.getUnitPrice());
                row.getCell(6).setCellValue(data.getNewUsers());
            }

            //4.通过输出流将excel下载到浏览器
            ServletOutputStream outputStream = response.getOutputStream();
            excel.write(outputStream);    //写回数据流对象即可下载

            excel.close();
            outputStream.close();

        } catch (IOException e) {
            throw new RuntimeException(e);
        }

    }
}
